package com.example.service.impl;

import com.example.mapper.*;
import com.example.pojo.Area;
import com.example.pojo.AreaContact;
import com.example.pojo.AreaLevel;
import com.example.pojo.Poi;
import com.example.service.AreaService;
import com.example.utils.ExcelUtils;
import org.springframework.aop.framework.AopContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.stream.Collectors;

@Service
public class AreaServiceImpl implements AreaService {
    @Autowired
    private AreaMapper areaMapper;
    @Autowired
    private ProductMapper productMapper;
    @Autowired
    private ProductAreaMapper productAreaMapper;
    @Autowired
    private ProductPushLogMapper productPushLogMapper;
    @Autowired
    private UserAreaMapper userAreaMapper;
    @Autowired
    private UserAreaGroupMapper userAreaGroupMapper;
    @Autowired
    private AreaContactMapper areaContactMapper;
    @Autowired
    private AreaLabelMapper areaLabelMapper;

    private Set<Integer> set;
    private int countUserArea;
    private int countProduct;
    private int countProductArea;
    private int countProductPushLog;
    private int countArea;
    private int countArea1;
    private int countUserAreaGroup;
    private int countAreaContact;
    private int countAreaLabel;
    private final int EARTH_RADIUS=6371;

    @Override
    public int reduceDormitoryAreaName() {
        int count=0;
        for (int i = 1; i <= 80; i++) {
            int num = areaMapper.reduceDormitoryAreaName("学生宿舍" + i + "号楼", i + "号宿舍楼");
            count=count+num;
        }
        return count;
    }

    @Override
    public int reduceDormitoryAreaNameByCampus(String campusSuffixName) {
        int count=0;
        for (int i = 1; i <= 80; i++) {
            List<Area> areaList = areaMapper.selectAreasByCampusSuffix(campusSuffixName+"学生宿舍" + i + "号楼");
            List<Area> areaList2=new ArrayList<>();
            List<Area> areaList3=new ArrayList<>();
            for (Area area : areaList) {
                String prefix=area.getAreaName().substring(0,area.getAreaName().indexOf(campusSuffixName)+2);
                if(prefix.length()<=3){
                    areaList2.add(area);
                }else {
                    areaList3.add(area);
                }
            }
            int num = areaMapper.reduceDormitoryAreaNameByCampus(campusSuffixName+"学生宿舍" + i + "号楼",
                    i + "号楼",areaList3);
            int num2 = areaMapper.reduceDormitoryAreaNameByCampus("学生宿舍" + i + "号楼",
                        i + "号楼",areaList2);
            count=count+num+num2;
        }
        return count;
    }

    @Override
    public String updateBranchCampusAreaAbbreviated(String originalPath) {
        List<Area> areaList = ExcelUtils.readExcel(originalPath, Area.class);
        if(areaList==null){
            return "文件不存在！";
        }
        int count=0;
        for (Area area : areaList) {
            if(area.getAreaName()==null){
                continue;
            }
            List<Area> areaList1 = areaMapper.selectByAreaNamePrefixAndAreaLevelAndReg(area.getAreaName(), 3, 8);
            if(areaList1.size()<=1){
                continue;
            }
            Area area1 = areaMapper.selectByAreaNameAndAreaLevelAndReg(area.getAreaName(), 3, 8);
            for (Area area2 : areaList1) {
                area2.setAreaAbbreviated(area1.getAreaAbbreviated());
            }
            count += areaMapper.batchUpdate(areaList1);
        }
        return "成功（更新了"+count+"条）";
    }

    @Override
    public String fillParentId(String originalPath) {
        List<Area> areaList = ExcelUtils.readExcel(originalPath, Area.class);
        if(areaList==null){
            return "文件不存在";
        }
        Integer firstId=null;
        Integer secondId=null;
        Integer thirdId=null;
        String number=null;
        for (Area area : areaList) {
            if(area.getAreaLevel()==1){
                Area area1 = areaMapper.selectByAreaNameAndAreaLevelAndReg(area.getAreaName(), 1, 0);
                if(area1==null){
                    firstId=null;
                    continue;
                }
                area.setId(area1.getId());
                area.setParentId(area1.getParentId());
                firstId=area1.getId();
            }else if(area.getAreaLevel()==2) {
                if(firstId==null){
                    secondId=null;
                    continue;
                }
                Area area2 = areaMapper.selectByAreaNameAndParentId(area.getAreaName(), firstId);
                if(area2==null){
                    secondId=null;
                    continue;
                }
                area.setId(area2.getId());
                area.setParentId(area2.getParentId());
                secondId=area2.getId();
            }else if(area.getAreaLevel()==3){
                if(secondId==null){
                    thirdId=null;
                    continue;
                }
                Area area3 = areaMapper.selectByAreaNameAndParentId(area.getAreaName(), secondId);
                if(area3==null){
                    thirdId=null;
                    continue;
                }
                area.setId(area3.getId());
                area.setParentId(area3.getParentId());
                thirdId=area3.getId();
                number=area.getNumber();
            }else {
                if(thirdId!=null&&area.getPreviousNumber().equals(number)){
                    area.setParentId(thirdId);
                }
            }
        }
        ExcelUtils.writeExcel(originalPath,areaList,Area.class);
        return "成功";
    }

    @Override
    public String addBookstoreArea(String originalPath, String bookstoreAreaPath) {
        List<Area> areaList = ExcelUtils.readExcel(originalPath, Area.class);
        List<AreaLevel> areaLevelList = ExcelUtils.readExcel(bookstoreAreaPath, AreaLevel.class);
        if(areaList==null||areaLevelList==null){
            return "文件不存在";
        }
        Integer firstId=null;
        Integer secondId=null;
        Integer thirdId=null;
        String number=null;
        for (AreaLevel areaLevel : areaLevelList) {
            for (int i = 0; i < areaList.size(); i++) {
                Area area = areaList.get(i);
                if(areaLevel.getProvince().equals(area.getAreaName())){
                    firstId=area.getId();
                    continue;
                }
                if(firstId!=null&&areaLevel.getCity().equals(area.getAreaName())&&firstId.equals(area.getParentId())){
                    secondId=area.getId();
                    firstId=null;
                    continue;
                }
                if(secondId!=null&&areaLevel.getArea().equals(area.getAreaName())&&secondId.equals(area.getParentId())){
                    thirdId=area.getId();
                    number=area.getNumber();
                    secondId=null;
                    continue;
                }
                if(thirdId!=null&&!thirdId.equals(area.getParentId())){
                    Area area1=new Area();
                    area1.setPreviousNumber(number);
                    area1.setParentId(thirdId);
                    area1.setAreaName(areaLevel.getVillage());
                    area1.setAreaLevel(4);
                    areaList.add(i,area1);
                    thirdId=null;
                    number=null;
                    break;
                }
                if(i==areaList.size()-1){
                    if(thirdId!=null){
                        Area area1=new Area();
                        area1.setPreviousNumber(number);
                        area1.setParentId(thirdId);
                        area1.setAreaName(areaLevel.getVillage());
                        area1.setAreaLevel(4);
                        areaList.add(i+1,area1);
                    }else {
                        System.out.println(areaLevel);
                    }
                    firstId=null;
                    secondId=null;
                    thirdId=null;
                    number=null;
                    break;
                }
            }
        }
        ExcelUtils.writeExcel(originalPath,areaList, Area.class);
        return "成功";
    }

    @Override
    public String findNoBookstoreArea(String areaPath, String bookstoreAreaPath) {
        List<Area> areaList = ExcelUtils.readExcel(areaPath, Area.class);
        List<AreaLevel> areaLevelList = ExcelUtils.readExcel(bookstoreAreaPath, AreaLevel.class);
        if(areaList==null||areaLevelList==null){
            return "文件不存在";
        }
        Integer firstId=null;
        Integer secondId=null;
        Integer thirdId=null;
        List<AreaLevel> noBookstoreAreaList=new ArrayList<>();
        for (AreaLevel areaLevel : areaLevelList) {
            for (int i = 0; i < areaList.size(); i++) {
                Area area=areaList.get(i);
                if(areaLevel.getProvince().equals(area.getAreaName())){
                    firstId=area.getId();
                    continue;
                }
                if(firstId!=null&&areaLevel.getCity().equals(area.getAreaName())&&firstId.equals(area.getParentId())){
                    secondId=area.getId();
                    firstId=null;
                    continue;
                }
                if(secondId!=null&&areaLevel.getArea().equals(area.getAreaName())&&secondId.equals(area.getParentId())){
                    thirdId=area.getId();
                    secondId=null;
                    continue;
                }
                if(thirdId!=null&&thirdId.equals(area.getParentId())){
                    if(areaLevel.getVillage().equals(area.getAreaName())){
                        thirdId=null;
                        break;
                    }
                }else if(thirdId!=null&&!thirdId.equals(area.getParentId())){
                    noBookstoreAreaList.add(areaLevel);
                    thirdId=null;
                    break;
                }
                if(i==areaList.size()-1){
                    noBookstoreAreaList.add(areaLevel);
                    firstId=null;
                    secondId=null;
                    thirdId=null;
                }
            }
        }
        ExcelUtils.writeExcel(bookstoreAreaPath,noBookstoreAreaList, AreaLevel.class);
        return "成功";
    }

    @Override
    public String fifthAreaFillParentId(String originalPath, String earlyVillagePath, String fourLevelDBPath, String fiveLevelPath) {
        List<Area> areaList = ExcelUtils.readExcel(originalPath, Area.class);
        List<Area> areaList1 = ExcelUtils.readExcel(earlyVillagePath, Area.class);
        List<Area> areaList2 = ExcelUtils.readExcel(fourLevelDBPath, Area.class);
        List<Area> areaList3 = ExcelUtils.readExcel(fiveLevelPath, Area.class);
        if(areaList==null||areaList1==null||areaList2==null||areaList3==null){
            return "文件不存在";
        }
        for (Area area : areaList) {
            if(area.getParentId()!=null){
                continue;
            }
            for (Area area1 : areaList1) {
                if(!area.getOriginalParentId().equals(area1.getOriginalParentId())
                        ||!area.getAreaAbbreviated().equals(area1.getAreaAbbreviated())){
                    continue;
                }
                List<Area> areas=new ArrayList<>();
                for (int i=0;i<areaList2.size();i++) {
                    Area area2=areaList2.get(i);
                    if(area2.getId()==null){
                        continue;
                    }
                    if(!area1.getOriginalParentId().equals(area2.getId())){
                        continue;
                    }
                    for(int j=i+1;j<areaList2.size();j++){
                        if(areaList2.get(j).getParentId().equals(area2.getId())){
                            areas.add(areaList2.get(j));
                        }else {
                            break;
                        }
                    }
                    int k=0;
                    String number=null;
                    for (Area area3 : areaList3) {
                        if(k==0&&(area3.getAreaLevel()!=3||!area3.getNumber().equals(area2.getNumber()))){
                            continue;
                        }
                        if(k==0){
                            k=1;
                            continue;
                        }
                        if(number==null&&!area1.getStreet().equals(area3.getAreaName())){
                            if(area3.getAreaLevel()<=3){
                                break;
                            }
                            continue;
                        }
                        if(number==null){
                            number=area3.getNumber();
                            continue;
                        }
                        if(number.equals(area3.getPreviousNumber())){
                            if(!area1.getAreaName().equals(area3.getAreaName())){
                                continue;
                            }
                            for (Area area4 : areas) {
                                if(!number.equals(area4.getNumber())){
                                    continue;
                                }
                                area.setParentId(area4.getId());
                                break;
                            }
                        }
                        break;
                    }
                    break;
                }
                break;
            }
        }
        ExcelUtils.writeExcel(originalPath,areaList, Area.class);
        return "成功";
    }

    @Override
    public String fifthAreaContainsFillParentId(String originalPath, String earlyVillagePath, String fourLevelDBPath, String fiveLevelPath) {
        List<Area> areaList = ExcelUtils.readExcel(originalPath, Area.class);
        List<Area> areaList1 = ExcelUtils.readExcel(earlyVillagePath, Area.class);
        List<Area> areaList2 = ExcelUtils.readExcel(fourLevelDBPath, Area.class);
        List<Area> areaList3 = ExcelUtils.readExcel(fiveLevelPath, Area.class);
        if(areaList==null||areaList1==null||areaList2==null||areaList3==null){
            return "文件不存在";
        }
        for (Area area : areaList) {
            if(area.getParentId()!=null){
                continue;
            }
            for (Area area1 : areaList1) {
                if(!area.getOriginalParentId().equals(area1.getOriginalParentId())
                        ||!area.getAreaAbbreviated().equals(area1.getAreaAbbreviated())){
                    continue;
                }
                List<Area> areas=new ArrayList<>();
                for (int i=0;i<areaList2.size();i++) {
                    Area area2=areaList2.get(i);
                    if(area2.getId()==null){
                        continue;
                    }
                    if(!area1.getOriginalParentId().equals(area2.getId())){
                        continue;
                    }
                    for(int j=i+1;j<areaList2.size();j++){
                        if(areaList2.get(j).getParentId().equals(area2.getId())){
                            areas.add(areaList2.get(j));
                        }else {
                            break;
                        }
                    }
                    int k=0;
                    String number=null;
                    for (Area area3 : areaList3) {
                        if(k==0&&(area3.getAreaLevel()!=3||!area3.getNumber().equals(area2.getNumber()))){
                            continue;
                        }
                        if(k==0){
                            k=1;
                            continue;
                        }
                        if(number==null&&!area1.getStreet().contains(area3.getAreaName())){
                            if(area3.getAreaLevel()<=3){
                                break;
                            }
                            continue;
                        }
                        if(number==null){
                            number=area3.getNumber();
                            continue;
                        }
                        if(number.equals(area3.getPreviousNumber())){
                            if(!area3.getAreaName().contains(area1.getAreaAbbreviated())){
                                continue;
                            }
                            for (Area area4 : areas) {
                                if(!number.equals(area4.getNumber())){
                                    continue;
                                }
                                area.setParentId(area4.getId());
                                break;
                            }
                        }
                        break;
                    }
                    break;
                }
                break;
            }
        }
        ExcelUtils.writeExcel(originalPath,areaList, Area.class);
        return "成功";
    }

    @Override
    public String fifthAreaNoStreetFillParentId(String originalPath, String earlyVillagePath, String fourLevelDBPath, String fiveLevelPath) {
        List<Area> areaList = ExcelUtils.readExcel(originalPath, Area.class);
        List<Area> areaList1 = ExcelUtils.readExcel(earlyVillagePath, Area.class);
        List<Area> areaList2 = ExcelUtils.readExcel(fourLevelDBPath, Area.class);
        List<Area> areaList3 = ExcelUtils.readExcel(fiveLevelPath, Area.class);
        if(areaList==null||areaList1==null||areaList2==null||areaList3==null){
            return "文件不存在";
        }
        for (Area area : areaList) {
            if(area.getParentId()!=null){
                continue;
            }
            for (Area area1 : areaList1) {
                if(!area.getOriginalParentId().equals(area1.getOriginalParentId())
                        ||!area.getAreaAbbreviated().equals(area1.getAreaAbbreviated())){
                    continue;
                }
                List<Area> areas=new ArrayList<>();
                for (int i=0;i<areaList2.size();i++) {
                    Area area2=areaList2.get(i);
                    if(area2.getId()==null){
                        continue;
                    }
                    if(!area1.getOriginalParentId().equals(area2.getId())){
                        continue;
                    }
                    for(int j=i+1;j<areaList2.size();j++){
                        if(areaList2.get(j).getParentId().equals(area2.getId())){
                            areas.add(areaList2.get(j));
                        }else {
                            break;
                        }
                    }
                    int k=0;
                    String number=null;
                    for (int n=0;i<areaList3.size();n++) {
                        Area area3=areaList3.get(n);
                        if(k==0&&(area3.getAreaLevel()!=3||!area3.getNumber().equals(area2.getNumber()))){
                            continue;
                        }
                        if(k==0){
                            k=1;
                            continue;
                        }
                        if(area3.getAreaLevel()>=4){
                            if(area3.getAreaLevel()!=5||!area1.getAreaName().equals(area3.getAreaName())){
//                            if(area3.getAreaLevel()!=5||!area1.getAreaName().contains(area3.getAreaName())){
                                continue;
                            }
                            for(int m=n-1;m>=0;m--){
                                if(areaList3.get(m).getAreaLevel()==4){
                                    number=areaList3.get(m).getNumber();
                                    break;
                                }
                            }
                            for (Area area4 : areas) {
                                if(!number.equals(area4.getNumber())){
                                    continue;
                                }
                                area.setParentId(area4.getId());
                                break;
                            }
                        }
                        break;
                    }
                    break;
                }
                break;
            }
        }
        ExcelUtils.writeExcel(originalPath,areaList, Area.class);
        return "成功";
    }

    @Override
    public String modifyDormitory1(String schoolPath,String teacherPath) {
        List<Area> areaList = ExcelUtils.readExcel(schoolPath, Area.class);
        List<Area> areaList1 = ExcelUtils.readExcel(teacherPath, Area.class);
        if(areaList==null||areaList1==null){
            return "文件不存在";
        }
        set = areaList1.stream().map(area -> area.getParentId()).collect(Collectors.toSet());
        Map<String,List<Integer>> map=new HashMap<>();
        countUserArea=0;
        countProduct=0;
        countProductArea=0;
        countProductPushLog=0;
        countArea=0;
        //获取当前对象的代理对象
        AreaServiceImpl proxy= (AreaServiceImpl) AopContext.currentProxy();
        List<String> campusList=new ArrayList<>();
        List<Area> tempList=new ArrayList<>();
        try {
            for (Area area : areaList) {
                if(area.getLasted()!=null){
                    continue;
                }
                List<Area> areaList2 = areaMapper.selectByParentIdAndExclude(area.getId(), "教师生活区");
                int n=0;
                int m=0;
                Set<String> set1=new HashSet<>();
                for (Area area1 : areaList2) {
                    if (area1.getAreaName().contains("校区")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("校区") + 2));
                    } else if (area1.getAreaName().contains("校本部")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("校本部") + 3));
                    } else if (area1.getAreaName().contains("新区")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("新区") + 2));
                    } else if (area1.getAreaName().contains("学院南路")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("学院南路") + 4));
                    } else if (area1.getAreaName().contains("学院路")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("学院路") + 3));
                    } else if (area1.getAreaName().contains("学院")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("学院") + 2));
                    } else if (area1.getAreaName().contains("金山科技园区")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("金山科技园区") + 6));
                    } else if (area1.getAreaName().contains("卫岗本部")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("卫岗本部") + 4));
                    } else if (area1.getAreaName().contains("珲春C")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("珲春C") + 2));
                    } else if (area1.getAreaName().equals("B01学生宿舍")) {
                        n=n+4;
                    } else if(area1.getAreaName().matches(".*\\d+.*")){
                        String[] split = area1.getAreaName().split("\\d");
                        if("".equals(split[0])||split[0].contains("学生宿舍")||split[0].contains("学生住宿")||split[0].contains("学生公寓")
                                ||split[0].equals("勤业")||split[0].equals("学")){
                            n++;
                        }else {
                            set1.add(split[0]);
                        }
                    } else {
                        m++;
                    }
                }
                if(m>n){  //先不处理
                    area.setLasted(2);//2是未处理的
                    continue;
                }
                if(n>=4||set1.isEmpty()){ //没有分校的宿舍
                    if(areaList2.size()<=20){
                        area.setLasted(1);
                        continue;
                    }
                    List<Integer> dataList=new ArrayList<>();
                    for (int i = 20; i < areaList2.size(); i++) {
                        dataList.add(areaList2.get(i).getId());
                    }
                    map.put(area.getId()+"&"+"#",dataList);
                }else {  //有分校的宿舍
                    for (String campus : set1) {
                        List<Integer> dataList=new ArrayList<>();
                        List<Area> areaList3 = areaMapper.selectByAreaNamePrefixAndParentId(campus, area.getId());
                        for (Area area1 : areaList3) {
                            dataList.add(area1.getId());
                        }
                        if(!campus.contains("校区")&&!campus.contains("校本部")&&!campus.equals("新区")&&!campus.contains("学院")
                                &&!campus.contains("金山科技园区")&&!campus.contains("卫岗本部")){
                            campus=campus+"校区";
                        }
                        if(campus.equals("学院南路")||campus.equals("学院路")){
                            campus=campus+"校区";
                        }
                        if(map.get(area.getId()+"&"+campus)!=null&&map.get(area.getId()+"&"+campus).size()>dataList.size()){
                            //防止 城西1号宿舍楼和城西校区男生宿舍楼，查城西校区时少的数据替换了多的数据
                            continue;
                        }
                        campusList.add(campus);
                        map.put(area.getId()+"&"+campus,dataList);
                    }
                }
                tempList.add(area); //记录已处理的学校
                if(map.size()>=10){
                    proxy.batchUpdateArea1(map);
                    map.clear();
                    for (Area area1 : tempList) {
                        area1.setLasted(1);//记录已更新完的学校
                    }
                    tempList.clear();
                }
            }
            if(map.size()>0){
                proxy.batchUpdateArea1(map);
                for (Area area1 : tempList) {
                    area1.setLasted(1);
                }
                tempList.clear();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println(campusList);
        ExcelUtils.writeExcel(schoolPath,areaList, Area.class);
        return "成功（更新用户地址："+countUserArea+"条 "+"更新商品："+countProduct+"条 "+"更新商品地址："+countProductArea+"条 "+"更新商品上架记录："+countProductPushLog+"条 "+"删除地址："+countArea+"条）";
    }

    @Transactional
    public void batchUpdateArea1(Map<String, List<Integer>> map) {
        List<Area> areaList=new ArrayList<>();
        List<Integer> list=new ArrayList<>();
        List<Area> areaList2=new ArrayList<>();
        for (String key : map.keySet()) {
            String[] split = key.split("&");
            if("#".equals(split[1])){
                if(!set.contains(Integer.valueOf(split[0]))){
                    Area area=new Area();
                    area.setParentId(Integer.valueOf(split[0]));
                    area.setAreaCode("");
                    area.setAreaName("教师生活区");
                    area.setAreaAbbreviated("");
                    area.setAreaLevel(4);
                    area.setLasted(1);
                    area.setRegionDivide(8);
                    area.setDelFlag(0);
                    area.setCreateBy(0);
                    areaList.add(area);
                }
                Area area=new Area();
                area.setParentId(Integer.valueOf(split[0]));
                area.setAreaCode("");
                area.setAreaName("图书馆");
                area.setAreaAbbreviated("");
                area.setAreaLevel(4);
                area.setLasted(1);
                area.setRegionDivide(8);
                area.setDelFlag(0);
                area.setCreateBy(0);
                areaList.add(area);

                Area area2=new Area();
                area2.setParentId(Integer.valueOf(split[0]));
                area2.setAreaCode("");
                area2.setAreaName("其它");
                area2.setAreaAbbreviated("");
                area2.setAreaLevel(4);
                area2.setLasted(1);
                area2.setRegionDivide(8);
                area2.setDelFlag(0);
                area2.setCreateBy(0);
                areaList.add(area2);
            }else {
                Area area=new Area();
                area.setParentId(Integer.valueOf(split[0]));
                area.setAreaCode("");
                area.setAreaName(split[1]);
                area.setAreaAbbreviated("");
                area.setAreaLevel(4);
                area.setLasted(1);
                area.setRegionDivide(8);
                area.setDelFlag(0);
                area.setCreateBy(0);
                areaList.add(area);
            }
        }
        areaMapper.batchInsert(areaList);
        for (String key : map.keySet()) {
            String[] split = key.split("&");
            for (Area area : areaList) {
                if("#".equals(split[1])){
                    if(Integer.valueOf(split[0]).equals(area.getParentId())&&"其它".equals(area.getAreaName())){
                        for (Integer i : map.get(key)) {
                            Area area2=new Area();
                            area2.setId(area.getId());
                            area2.setOriginalId(i);
                            areaList2.add(area2);
                        }
                        break;
                    }
                }else {
                    if(Integer.valueOf(split[0]).equals(area.getParentId())&&split[1].equals(area.getAreaName())){
                        for (Integer i : map.get(key)) {
                            Area area2=new Area();
                            area2.setId(area.getId());
                            area2.setOriginalId(i);
                            areaList2.add(area2);
                        }
                        break;
                    }
                }
            }
        }
        int u= userAreaMapper.batchUpdateByVillageId(areaList2);
        int n= productMapper.batchUpdateByAreaId(areaList2);
        int k= productAreaMapper.batchUpdateByVillageId(areaList2);
        int m= productPushLogMapper.batchUpdateByVillageId(areaList2);
        //删除校区宿舍楼和20号楼以后的宿舍
        for (List<Integer> value : map.values()) {
            for (Integer i : value) {
                list.add(i);
            }
        }
        int a = areaMapper.batchDelete(list);
        countUserArea+=u;
        countProduct+=n;
        countProductArea+=k;
        countProductPushLog+=m;
        countArea+=a;
        System.out.println("更新用户地址："+u+"条 "+"更新商品："+n+"条 "+"更新商品地址："+k+"条 "+"更新商品上架记录："+m+"条 "+"删除地址："+a+"条");
    }

    @Override
    public String modifyDormitory(String schoolPath) {
        List<Area> areaList = ExcelUtils.readExcel(schoolPath, Area.class);
        if(areaList==null){
            return "文件不存在";
        }
        Map<String,List<Integer>> map=new HashMap<>();
        countUserArea=0;
        countProduct=0;
        countProductArea=0;
        countProductPushLog=0;
        countArea=0;
        //获取当前对象的代理对象
        AreaServiceImpl proxy= (AreaServiceImpl) AopContext.currentProxy();
        List<String> campusList=new ArrayList<>();
        List<Area> tempList=new ArrayList<>();
        try {
            for (Area area : areaList) {
                if(area.getLasted()!=null){
                    continue;
                }
                List<Area> areaList2 = areaMapper.selectByParentIdAndExclude(area.getId(), "教师生活区");
                int n=0;
                int m=0;
                Set<String> set1=new HashSet<>();
                for (Area area1 : areaList2) {
                    if (area1.getAreaName().contains("校区")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("校区") + 2));
                    } else if (area1.getAreaName().contains("校本部")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("校本部") + 3));
                    } else if (area1.getAreaName().contains("新区")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("新区") + 2));
                    } else if (area1.getAreaName().contains("学院南路")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("学院南路") + 4));
                    } else if (area1.getAreaName().contains("学院路")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("学院路") + 3));
                    } else if (area1.getAreaName().contains("学院")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("学院") + 2));
                    } else if (area1.getAreaName().contains("金山科技园区")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("金山科技园区") + 6));
                    } else if (area1.getAreaName().contains("卫岗本部")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("卫岗本部") + 4));
                    } else if (area1.getAreaName().contains("珲春C")) {
                        set1.add(area1.getAreaName().substring(0, area1.getAreaName().indexOf("珲春C") + 2));
                    } else if (area1.getAreaName().equals("B01学生宿舍")) {
                        n=n+4;
                    } else if(area1.getAreaName().matches(".*\\d+.*")){
                        String[] split = area1.getAreaName().split("\\d");
                        if("".equals(split[0])||split[0].contains("学生宿舍")||split[0].contains("学生住宿")||split[0].contains("学生公寓")
                                ||split[0].equals("勤业")||split[0].equals("学")){
                            n++;
                        }else {
                            set1.add(split[0]);
                        }
                    } else {
                        m++;
                    }
                }
                if(m>n){  //先不处理
                    area.setLasted(2);//2是未处理的
                    continue;
                }
                if(n>=4||set1.isEmpty()){ //没有分校的宿舍
                    area.setLasted(3); //没有分校的标记为3
                    continue;
                }else {  //有分校的宿舍
                    for (String campus : set1) {
                        List<Integer> dataList=new ArrayList<>();
                        List<Area> areaList3 = areaMapper.selectByAreaNamePrefixAndParentId(campus, area.getId());
                        for (Area area1 : areaList3) {
                            dataList.add(area1.getId());
                        }
                        if(!campus.contains("校区")&&!campus.contains("校本部")&&!campus.equals("新区")&&!campus.contains("学院")
                                &&!campus.contains("金山科技园区")&&!campus.contains("卫岗本部")){
                            campus=campus+"校区";
                        }
                        if(campus.equals("学院南路")||campus.equals("学院路")){
                            campus=campus+"校区";
                        }
                        if(map.get(area.getId()+"&"+campus)!=null&&map.get(area.getId()+"&"+campus).size()>dataList.size()){
                            //防止 城西1号宿舍楼和城西校区男生宿舍楼，查城西校区时少的数据替换了多的数据
                            continue;
                        }
                        campusList.add(campus);
                        map.put(area.getId()+"&"+campus,dataList);
                    }
                }
                tempList.add(area); //记录已处理的学校
                if(map.size()>=10){
                    proxy.batchUpdateArea(map);
                    map.clear();
                    for (Area area1 : tempList) {
                        area1.setLasted(1);//记录已更新完的学校
                    }
                    tempList.clear();
                }
            }
            if(map.size()>0){
                proxy.batchUpdateArea(map);
                for (Area area1 : tempList) {
                    area1.setLasted(1);
                }
                tempList.clear();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println(campusList);
        ExcelUtils.writeExcel(schoolPath,areaList, Area.class);
        return "成功（更新用户地址："+countUserArea+"条 "+"更新商品："+countProduct+"条 "+"更新商品地址："+countProductArea+"条 "+"更新商品上架记录："+countProductPushLog+"条 "+"删除地址："+countArea+"条）";
    }

    @Override
    public String gaodeDataFillAreaId(String originalPath) {
        List<Poi> poiList = ExcelUtils.readExcel(originalPath, Poi.class);
        List<AreaLevel> areaLevelList = areaMapper.selectAllUpAreaFourLevelVillage();
        if(poiList==null||areaLevelList==null){
            return "文件不存在";
        }
        for (Poi poi : poiList) {
            if(poi.getVillageId()!=null || poi.getTownship()==null){
                continue;
            }
//            String township=poi.getTownship();
//            if(township.contains("办事处")){
//                township=township.replace("办事处","");
//            }
//            if(township.contains("管理处")){
//                township=township.replace("管理处","");
//            }
//            if(township.length()>=4&&township.substring(township.length()-4).equals("片区街道")){
//                township=township.replaceAll("片区街道$","");
//            }else if(township.length()>=4&&township.substring(township.length()-4).equals("街道地区")){
//                township=township.replaceAll("街道地区$","");
//            }else if(township.length()>=3&&township.substring(township.length()-3).equals("街街道")){
//                township=township.replaceAll("街街道$","");
//            }else if(township.length()>=3&&township.substring(township.length()-3).equals("路街道")){
//                township=township.replaceAll("路街道$","");
//            }else if(township.substring(township.length()-2).equals("街道")){
//                township=township.replaceAll("街道$","");
//            }else if(township.substring(township.length()-2).equals("地区")){
//                township=township.replaceAll("地区$","");
//            }else if(township.substring(township.length()-1).equals("镇")){
//                township=township.replaceAll("镇$","");
//            }else if(township.substring(township.length()-1).equals("乡")){
//                township=township.replaceAll("乡$","");
//            }
            for (AreaLevel areaLevel : areaLevelList) {
//                String village=areaLevel.getVillage();
//                if(village.contains("办事处")){
//                    village=village.replace("办事处","");
//                }
//                if(village.contains("管理处")){
//                    village=village.replace("管理处","");
//                }
//                if(village.length()>=4&&village.substring(village.length()-4).equals("片区街道")){
//                    village=village.replaceAll("片区街道$","");
//                }else if(village.length()>=4&&village.substring(village.length()-4).equals("街道地区")){
//                    village=village.replaceAll("街道地区$","");
//                }else if(village.length()>=3&&village.substring(village.length()-3).equals("街街道")){
//                    village=village.replaceAll("街街道$","");
//                }else if(village.length()>=3&&village.substring(village.length()-3).equals("路街道")){
//                    village=village.replaceAll("路街道$","");
//                }else if(village.substring(village.length()-2).equals("街道")){
//                    village=village.replaceAll("街道$","");
//                }else if(village.substring(village.length()-2).equals("地区")){
//                    village=village.replaceAll("地区$","");
//                }else if(village.substring(village.length()-1).equals("镇")){
//                    village=village.replaceAll("镇$","");
//                }else if(village.substring(village.length()-1).equals("乡")){
//                    village=village.replaceAll("乡$","");
//                }
//
//                if(!township.equals(village)||!areaLevel.getArea().equals(poi.getAdname())){
//                    continue;
//                }

                if(!poi.getTownship().equals(areaLevel.getVillage())||!areaLevel.getArea().equals(poi.getAdname())
                        /*||!areaLevel.getCity().equals(poi.getCityname())*/){
                    continue;
                }
                poi.setVillageId(areaLevel.getVillageId());
                break;
            }
        }
        ExcelUtils.writeExcel(originalPath,poiList, Poi.class);
        return "成功";
    }

    @Override
    public String latitudeLongitudeFillAreaId(String originalPath, String areaPath) {
        List<Poi> poiList = ExcelUtils.readExcel(originalPath, Poi.class);
        List<AreaLevel> areaLevelList = ExcelUtils.readExcel(areaPath, AreaLevel.class);
        if(poiList==null||areaLevelList==null){
            return "文件不存在";
        }
        for (Poi poi : poiList) {
            if(poi.getVillageId()!=null){
                continue;
            }
            double minDistance=0.0;
            Integer villageId=null;
            for (AreaLevel areaLevel : areaLevelList) {
                if(!areaLevel.getArea().equals(poi.getAdname())){
                    continue;
                }
                String[] split = poi.getLocation().split(",");
                String[] split2 = areaLevel.getLocation().split(",");
                double distance = haversineDistance(Double.parseDouble(split[1]), Double.parseDouble(split[0])
                        , Double.parseDouble(split2[1]), Double.parseDouble(split2[0]));
//                if(distance>50){
//                    continue;
//                }
                if(minDistance==0.0||distance<minDistance){
                    minDistance=distance;
                    villageId=areaLevel.getVillageId();
                }
            }
            if(villageId!=null){
                poi.setVillageId(villageId);
            }
        }
        ExcelUtils.writeExcel(originalPath,poiList, Poi.class);
        return "成功";
    }

    @Override
    public String removeStreetKindergartenSame(String originalPath) {
        List<Poi> poiList = ExcelUtils.readExcel(originalPath, Poi.class);
        if(poiList==null){
            return "文件不存在";
        }
        for (int i = 0; i < poiList.size(); i++) {
            Poi poi=poiList.get(i);
            for (int j = i+1; j < poiList.size(); j++) {
                Poi poi1=poiList.get(j);
                if(!poi.getName().equals(poi1.getName())||!poi.getVillageId().equals(poi1.getVillageId())){
                    continue;
                }
                if(poi1.getTelephone()!=null){
                    if(poi.getTelephone()==null){
                        poi.setTelephone(poi1.getTelephone());
                    }else {
                        poi.setTelephone(poi.getTelephone()+";"+poi1.getTelephone());
                    }
                }
                poiList.remove(j);
                j--;
            }
        }
        ExcelUtils.writeExcel(originalPath,poiList,Poi.class);
        return "成功";
    }

    @Override
    public String fillGroupLeader(String originalPath) { //先更新有手机号，再更新固话
        List<Poi> poiList = ExcelUtils.readExcel(originalPath, Poi.class);
        if(poiList==null){
            return "文件不存在";
        }
        List<Area> areaList=new ArrayList<>();
        int num=0;
        for (Poi poi : poiList) {
            if(poi.getUserId()==null||poi.getUserId()==-3||(poi.getCategory()!=null&&poi.getCategory()==2)){ //有多个手机号的都用作更新组长，防止name为空的自己手机号，name不为空的别人手机号，如果忽略name为空的这个小组就没组长了
                continue;
            }
            Area area = areaMapper.selectById(poi.getGroupId());
            if(area.getCreateBy()!=0){
                continue;
            }
            area.setCreateBy(poi.getUserId());
            areaList.add(area);
            if(areaList.size()%1000==0){
                num+=areaMapper.batchUpdate(areaList);
                areaList.clear();
            }
        }
        if(areaList.size()>0){
            num+=areaMapper.batchUpdate(areaList);
        }
        return "成功（更新："+num+"条）";
    }

    @Override
    public String latitudeLongitudeFillCommunityId(String poiPath, String areaPath) {
        List<Poi> poiList = ExcelUtils.readExcel(poiPath, Poi.class);
        List<AreaLevel> areaLevelList = ExcelUtils.readExcel(areaPath, AreaLevel.class);
        if(poiList==null||areaLevelList==null){
            return "文件不存在";
        }
        for (Poi poi : poiList) {
            if(poi.getCommunityId()!=null){
                continue;
            }
            double minDistance=0.0;
            Integer communityId=null;
            for (AreaLevel areaLevel : areaLevelList) {
                if(areaLevel.getLocation()==null||!poi.getAdname().equals(areaLevel.getArea())){
                    continue;
                }
                String[] split = poi.getLocation().split(",");
                String[] split1 = areaLevel.getLocation().split(",");
                double distance = haversineDistance(Double.parseDouble(split[1]), Double.parseDouble(split[0])
                        , Double.parseDouble(split1[1]), Double.parseDouble(split1[0]));
//                if(distance>5){
//                    continue;
//                }
                if(minDistance==0.0||distance<minDistance){
                    minDistance=distance;
                    communityId=areaLevel.getCommunityId();
                }
            }
            if(communityId!=null){
                poi.setCommunityId(communityId);
            }
        }
        ExcelUtils.writeExcel(poiPath,poiList, Poi.class);
        return "成功";
    }

    @Override
    public String calculateBookstore(String poiPath, String areaPath) {
        List<Poi> poiList = ExcelUtils.readExcel(poiPath, Poi.class);
        List<AreaLevel> areaLevelList = ExcelUtils.readExcel(areaPath, AreaLevel.class);
        if(poiList==null||areaLevelList==null){
            return "文件不存在";
        }
        for (Poi poi : poiList) {
            if(poi.getCategory()!=null){
                continue;
            }
            for (AreaLevel areaLevel : areaLevelList) {
//                if(!poi.getCityname().equals(areaLevel.getCity())){
//                    continue;
//                }
                if(!poi.getAdname().equals(areaLevel.getArea())){
                    continue;
                }
                String[] split = poi.getLocation().split(",");
                String[] split1 = areaLevel.getLocation().split(",");
                double distance = haversineDistance(Double.parseDouble(split[1]), Double.parseDouble(split[0])
                        , Double.parseDouble(split1[1]), Double.parseDouble(split1[0]));
                if(distance>1){
                    continue;
                }
                poi.setCategory(3);
                break;
            }
        }
        ExcelUtils.writeExcel(poiPath,poiList, Poi.class);
        return "成功";
    }

    @Override
    public String fillUniversityArea(String poiPath, String areaPath) {
        List<Poi> poiList = ExcelUtils.readExcel(poiPath, Poi.class);
        List<AreaLevel> areaLevelList = ExcelUtils.readExcel(areaPath, AreaLevel.class);
        if(poiList==null||areaLevelList==null){
            return "文件不存在";
        }
        for (Poi poi : poiList) {
            if(poi.getAreaId()!=null&&poi.getVillageId()!=null){
                continue;
            }
            for (AreaLevel areaLevel : areaLevelList) {
                if(!poi.getCategory().equals(areaLevel.getUniversityId())){
                    continue;
                }
                if(areaLevel.getDelFlag()==0){
                    poi.setAreaId(poi.getCategory());
                    String suShe="1号宿舍楼";
                    if(areaLevel.getUniversity().equals("河南大学")){
                        suShe="明伦校区";
                    }else if(areaLevel.getUniversity().equals("河南大学（郑州校区）")){
                        suShe="龙子湖校区";
                    }else if(areaLevel.getUniversity().equals("中国地质大学（武汉）")){
                        suShe="南望山校区";
                    }else if(areaLevel.getUniversity().equals("四川农业大学（成都）")){
                        suShe="成都校区";
                    }else if(areaLevel.getUniversity().equals("华南师范大学")){
                        suShe="石牌校区";
                    }else if(areaLevel.getUniversity().equals("华南理工大学")){
                        suShe="五山校区";
                    }else if(areaLevel.getUniversity().equals("西北政法大学")){
                        suShe="长安校区";
                    }
                    Area area = areaMapper.selectByAreaNameAndParentId(suShe, poi.getAreaId());
                    if(area==null){
                        String[] arr={"学生住宿1号楼","学生宿舍1栋","学生宿舍1舍","北区第一学生宿舍","勤业1号楼","学生公寓1栋","天清苑1栋","学生公寓1号楼","龙泉南楼","A栋学生公寓","1号学生公寓"};
                        for (String str : arr) {
                            area = areaMapper.selectByAreaNameAndParentId(str, poi.getAreaId());
                            if(area!=null){
                                break;
                            }
                        }
                    }
                    if(area==null){ //找主校区
                        List<String> branchList=new ArrayList<>();
                        for (AreaLevel level : areaLevelList) {
                            if(level.getUniversity().startsWith(areaLevel.getUniversity())
                                    &&!level.getUniversity().equals(areaLevel.getUniversity())){
                                branchList.add(level.getUniversity());
                            }
                        }
                        List<Area> areaList = areaMapper.selectByParentId(poi.getAreaId());
                        for (String branch : branchList) {
                            String[] split = branch.split("大学");
                            if(split.length<2){ //如果是学院就不往下
                                continue;
                            }
                            for (int i = 0; i < areaList.size(); i++) {
                                Area area1=areaList.get(i);
                                if(area1.getAreaName().equals(split[1])||(area1.getAreaName().contains("学院")&&!area1.getAreaName().contains("学院路")&&!area1.getAreaName().contains("学院南路"))){
                                    areaList.remove(i);
                                    i--;
                                }
                            }
                        }
                        if(areaList.size()==1){
                            poi.setVillageId(areaList.get(0).getId());
                            break;
                        }
                    }
                    if(area==null){
                        break;
                    }
                    poi.setVillageId(area.getId());
                    break;
                }
                String university=null;
                String campus=null;
                if(areaLevel.getUniversity().equals("华南师范大学大学城校区")){
                    university="华南师范大学";
                    campus="大学城校区";
                }else if(areaLevel.getUniversity().equals("中国地质大学（武汉）未来城校区")){
                    university="中国地质大学（武汉）";
                    campus="未来城校区";
                }else if(areaLevel.getUniversity().equals("四川农业大学都江堰校区")){
                    university="四川农业大学（成都）";
                    campus="都江堰校区";
                }else {
                    String[] split = areaLevel.getUniversity().split("大学");
                    university=split[0] + "大学";
                    campus=split[1];
                }
                Area area = areaMapper.selectByAreaNameAndAreaLevelAndReg(university, 3, 8);
                if(area==null){
                    break;
                }
                Area area1 = areaMapper.selectByAreaNameAndParentId(campus, area.getId());
                if(area1==null){
                    break;
                }
                poi.setAreaId(area.getId());
                poi.setVillageId(area1.getId());
                break;
            }
        }
        ExcelUtils.writeExcel(poiPath,poiList, Poi.class);
        return "成功";
    }

    @Override
    public String updateLonLatByVillageId(String originalPath) {
        List<AreaLevel> areaLevelList = ExcelUtils.readExcel(originalPath, AreaLevel.class);
        if(areaLevelList==null){
            return "文件不存在";
        }
        List<Area> areaList=new ArrayList<>();
        int count=0;
        for (AreaLevel areaLevel : areaLevelList) {
            if(areaLevel.getVillageId()==null||areaLevel.getVillage()==null||areaLevel.getLocation()==null){
                continue;
            }
            Area area = areaMapper.selectByAreaNameAndParentId(areaLevel.getVillage() + "换书小组", areaLevel.getVillageId());
            if(area==null){
                System.out.println(areaLevel.getArea()+" "+areaLevel.getVillage()+" 没查到数据");
                continue;
            }
            if(!area.getLongitude().equals(0.00)&&!area.getLatitude().equals(0.00)){
                continue;
            }
            String[] split = areaLevel.getLocation().split(",");
            area.setLongitude(Double.valueOf(split[0]));
            area.setLatitude(Double.valueOf(split[1]));
            areaList.add(area);
            if(areaList.size()%1000==0){
                count+=areaMapper.batchUpdate(areaList);
                areaList.clear();
            }
        }
        if(areaList.size()>0){
            count+=areaMapper.batchUpdate(areaList);
        }
        return "成功（更新地址："+count+"条）";
    }

    @Override
    public String updateLonLatByCommunityId(String originalPath) {
        List<AreaLevel> areaLevelList = ExcelUtils.readExcel(originalPath, AreaLevel.class);
        if(areaLevelList==null){
            return "文件不存在";
        }
        List<Area> areaList=new ArrayList<>();
        int count=0;
        for (AreaLevel areaLevel : areaLevelList) {
            if(areaLevel.getCommunityId()==null||areaLevel.getLocation()==null){
                continue;
            }
            Area area=new Area();
            area.setId(areaLevel.getCommunityId());
            String[] split = areaLevel.getLocation().split(",");
            area.setLongitude(Double.valueOf(split[0]));
            area.setLatitude(Double.valueOf(split[1]));
            areaList.add(area);
            if(areaList.size()%1000==0){
                count+=areaMapper.batchUpdateLonLat(areaList);
                areaList.clear();
            }
        }
        if(areaList.size()>0){
            count+=areaMapper.batchUpdateLonLat(areaList);
        }
        return "成功（更新地址："+count+"条）";
    }

    @Override
    public String updateLonLatDormitory(String schoolPath, String mergeCampusPath) {
        List<AreaLevel> areaLevelList = ExcelUtils.readExcel(schoolPath, AreaLevel.class);
        List<Area> areaList = ExcelUtils.readExcel(mergeCampusPath, Area.class);
        if(areaLevelList==null||areaList==null){
            return "文件不存在";
        }
        Set<Integer> mergeCampusIds = areaList.stream().filter(area -> area.getLasted() == 1)
                .map(area -> area.getId()).collect(Collectors.toSet());
        Set<Integer> noNumberDormitoryIds = areaList.stream().filter(area -> area.getLasted() == 2)
                .map(area -> area.getId()).collect(Collectors.toSet());
        List<Area> areas=new ArrayList<>();
        int m=0;
        int countArea=0;
        try {
            for (AreaLevel areaLevel : areaLevelList) {
                if(areaLevel.getAreaId()!=null){ //做标记，用了经纬度的大学就跳过，主要作用在合并校区的大学
                    continue;
                }
                List<Area> areaList1 = areaMapper.selectGroupByThirdId(areaLevel.getUniversityId());
                if(areaList1==null||areaList1.isEmpty()){
                    System.out.println("无数据："+areaLevel.getUniversityId());
                    continue;
                }
                if(mergeCampusIds.contains(areaLevel.getUniversityId())){
                    //合并校区的
                    int count=0;
                    for (Area area : areaList1) {
                        String campus = (areaLevel.getUniversity() + area.getAreaName()).replace("换书小组", "");
                        for (AreaLevel level : areaLevelList) {
                            if(level.getUniversity().equals(campus)){
                                count++;
                                if(area.getLongitude()!=null&&!area.getLongitude().equals(0.0)){
                                    break;
                                }
                                String[] split = level.getLocation().split(",");
                                area.setLongitude(Double.valueOf(split[0]));
                                area.setLatitude(Double.valueOf(split[1]));
                                areas.add(area);
                                level.setAreaId(1); //1代表该学校的宿舍经纬度已填充
                                break;
                            }
                        }
                    }
                    if(count==areaList1.size()-1){ //设置主校区经纬度
                        for (Area area : areaList1) {
                            if(area.getLongitude()==null||area.getLongitude().equals(0.0)){
                                String[] split = areaLevel.getLocation().split(",");
                                area.setLongitude(Double.valueOf(split[0]));
                                area.setLatitude(Double.valueOf(split[1]));
                                areas.add(area);
                                areaLevel.setAreaId(1); //1代表该学校的宿舍经纬度已填充
                                break;
                            }
                        }
                    }
                }else {
                    //其他学校处理。判断宿舍换书小组是否有数字，大于等于4个或在noNumberDormitoryIds中就认为是非合并校区的。
                    int n=0;
                    for (Area area : areaList1) {
                        if(area.getAreaName().matches(".*\\d+.*")){
                            n++;
                        }
                        if(n>=4){
                            break;
                        }
                    }
                    if(n>=4||noNumberDormitoryIds.contains(areaLevel.getUniversityId())){
                        String[] split = areaLevel.getLocation().split(",");
                        for (Area area : areaList1) {
                            if(area.getLongitude()==null||area.getLongitude().equals(0.0)){
                                area.setLongitude(Double.valueOf(split[0]));
                                area.setLatitude(Double.valueOf(split[1]));
                                areas.add(area);
                            }
                        }
                        areaLevel.setAreaId(1); //1代表该学校的宿舍经纬度已填充
                    }else {
                        //不在上诉情况中的输出
                        System.out.println(areaLevel.getUniversityId());
                    }
                }
                m++;
                if(m%20==0){
                    countArea+=areaMapper.batchUpdate(areas);
                    areas.clear();
                }
            }
            if(areas.size()>0){
                countArea+=areaMapper.batchUpdate(areas);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        ExcelUtils.writeExcel(schoolPath,areaLevelList, AreaLevel.class);
        return "成功（更新地址："+countArea+"条）";
    }

    @Override
    public String mergerDistrict(String originalPath) { //建一张t_area_contact临时表，取消联合主键，要不然会出错
        List<Area> areaList = ExcelUtils.readExcel(originalPath, Area.class);
        if(areaList==null){
            return "文件不存在";
        }
        List<Area> areas=new ArrayList<>();
        List<Area> areas2=new ArrayList<>();
        Integer tempId=null;
        countArea=0;
        countArea1=0;
        countUserArea=0;
        countProductArea=0;
        countProductPushLog=0;
        countUserAreaGroup=0;
        countAreaContact=0;
        countAreaLabel=0;
        int m=0;
        AreaServiceImpl proxy = (AreaServiceImpl) AopContext.currentProxy();
        try {
            for (Area area : areaList) {
                Area area1 = areaMapper.selectById(area.getId());
                if(area.getAreaName()!=null){
                    if(!areas.isEmpty()&&m%20==0){
                        proxy.batchUpdateDistrict(areas,areas2);
                    }
                    m++;

                    area1.setAreaName(area.getAreaName());
                    if(area.getAreaName().matches(".+市$")){
                        area1.setAreaAbbreviated(area.getAreaName().replaceAll("市$",""));
                    }else if(area.getAreaName().matches(".+地区$")){
                        area1.setAreaAbbreviated(area.getAreaName().replaceAll("地区$",""));
                    }else if(area.getAreaName().matches(".+林区$")){
                        area1.setAreaAbbreviated(area.getAreaName().replaceAll("林区$",""));
                    }else if(area.getAreaName().matches(".+盟$")){
                        area1.setAreaAbbreviated(area.getAreaName().replaceAll("盟$",""));
                    }else {
                        System.out.println("简称没更新："+area);
                    }
                    tempId=area.getId();
                }else {
                    area1.setDelFlag(1);
                    Area area2=new Area();
                    area2.setId(tempId);
                    area2.setOriginalId(area.getId());
                    areas2.add(area2);
                }
                areas.add(area1);
            }
            if(areas.size()>0){
                proxy.batchUpdateDistrict(areas,areas2);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "成功（更新地址："+countArea+"条，更新地址1："+countArea1+"条，更新用户地址："+countUserArea
                +"条，更新商品地址："+countProductArea+"条，更新商品上架记录："+countProductPushLog+"条，更新换书小组："+countUserAreaGroup
                +"条，更新书市关联："+countAreaContact+"条，更新地址标签："+countAreaLabel+"条）";
    }

    @Override
    public String mergerCityOfZhixiashiUniversity(String originalPath) {
        List<Area> areaList = ExcelUtils.readExcel(originalPath, Area.class);
        if(areaList==null){
            return "文件不存在";
        }
        List<Area> areas=new ArrayList<>();
        List<Area> areas2=new ArrayList<>();
        Integer tempId=null;
        countArea=0;
        countArea1=0;
        countUserArea=0;
        countProductArea=0;
        int m=0;
        AreaServiceImpl proxy = (AreaServiceImpl) AopContext.currentProxy();
        try {
            for (Area area : areaList) {
                Area area1 = areaMapper.selectById(area.getId());
                if(area.getAreaName()!=null){
                    if(!areas.isEmpty()&&m%10==0){
                        proxy.batchUpdateCity(areas,areas2);
                    }
                    m++;

                    area1.setAreaName(area.getAreaName());
                    areas.add(area1);
                    tempId=area.getId();
                }else {
                    if(!area.getId().equals(57)&&!area.getId().equals(322)
                            &&!area.getId().equals(631010)&&!area.getId().equals(630959)){
                        area1.setDelFlag(1);
                        areas.add(area1);
                    }
                    Area condition=new Area();
                    condition.setParentId(area.getId());
                    condition.setAreaLevel(3);
                    condition.setRegionDivide(8);
                    List<Area> areaList1 = areaMapper.selectByArea(condition);
                    for (Area area2 : areaList1) {
                        area2.setParentId(tempId);
                        areas2.add(area2);
                    }
                }
            }
            if(areas.size()>0){
                proxy.batchUpdateCity(areas,areas2);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "成功（更新地址："+countArea+"条，更新地址1："+countArea1+"条，更新用户地址："+countUserArea
                +"条，更新商品地址："+countProductArea+"条）";
    }

    @Transactional
    public void batchUpdateCity(List<Area> areas, List<Area> areas2) {
        int a=areaMapper.batchUpdate(areas);
        int a1=areaMapper.batchUpdate(areas2);
        int ua=userAreaMapper.batchUpdateByUniversityId(areas2);
        int pa=productAreaMapper.batchUpdateByUniversityId(areas2);

        areas.clear();
        areas2.clear();
        countArea+=a;
        countArea1+=a1;
        countUserArea+=ua;
        countProductArea+=pa;
    }

    @Transactional
    public void batchUpdateDistrict(List<Area> areas, List<Area> areas2) {
        int a=areaMapper.batchUpdate(areas);
        int a1=areaMapper.batchUpdateByParentId(areas2);
        int ua=userAreaMapper.batchUpdateByAreaId(areas2);
        int pa=productAreaMapper.batchUpdateByAreaId(areas2);
        int ppl=productPushLogMapper.batchUpdateByAreaId(areas2);
        int uag=userAreaGroupMapper.batchUpdateByAreaId(areas2);
        int ac=areaContactMapper.batchUpdateByAreaId(areas2);  //更新sql换成临时表名
        int ac2=areaContactMapper.batchUpdateByContactAreaId(areas2);  //更新sql换成临时表名
        int al=areaLabelMapper.batchUpdateByAreaId(areas2);

        areas.clear();
        areas2.clear();
        countArea+=a;
        countArea1+=a1;
        countUserArea+=ua;
        countProductArea+=pa;
        countProductPushLog+=ppl;
        countUserAreaGroup+=uag;
        countAreaContact+=(ac+ac2);
        countAreaLabel+=al;
    }

    public double haversineDistance(double lat1, double lon1, double lat2, double lon2) {
        // 将角度转换为弧度
        lat1 = Math.toRadians(lat1);
        lon1 = Math.toRadians(lon1);
        lat2 = Math.toRadians(lat2);
        lon2 = Math.toRadians(lon2);

        // Haversine公式
        double dlon = lon2 - lon1;
        double dlat = lat2 - lat1;
        double a = Math.pow(Math.sin(dlat / 2), 2) + Math.cos(lat1) * Math.cos(lat2) * Math.pow(Math.sin(dlon / 2), 2);
        double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
        double distance = EARTH_RADIUS * c;

        return distance; // 返回距离，单位为公里
    }

    @Transactional
    public void batchUpdateArea(Map<String, List<Integer>> map) {
        List<Area> areaList=new ArrayList<>();
        List<Integer> list=new ArrayList<>();
        List<Area> areaList2=new ArrayList<>();
        for (String key : map.keySet()) {
            String[] split = key.split("&");
            Area area=new Area();
            area.setParentId(Integer.valueOf(split[0]));
            area.setAreaCode("");
            area.setAreaName(split[1]);
            area.setAreaAbbreviated("");
            area.setAreaLevel(4);
            area.setLasted(1);
            area.setRegionDivide(8);
            area.setLongitude(0.0);
            area.setLatitude(0.0);
            area.setDelFlag(0);
            area.setCreateBy(0);
            areaList.add(area);
        }
        areaMapper.batchInsert(areaList);
        for (String key : map.keySet()) {
            String[] split = key.split("&");
            for (Area area : areaList) {
                if(Integer.valueOf(split[0]).equals(area.getParentId())&&split[1].equals(area.getAreaName())){
                    for (Integer i : map.get(key)) {
                        Area area2=new Area();
                        area2.setId(area.getId());
                        area2.setOriginalId(i);
                        areaList2.add(area2);
                    }
                    break;
                }
            }
        }
        int u= userAreaMapper.batchUpdateByVillageId(areaList2);
        int n= productMapper.batchUpdateByAreaId(areaList2);
        int k= productAreaMapper.batchUpdateByVillageId(areaList2);
        int m= productPushLogMapper.batchUpdateByVillageId(areaList2);
        //删除校区宿舍楼
        for (List<Integer> value : map.values()) {
            for (Integer i : value) {
                list.add(i);
            }
        }
        int a = areaMapper.batchDelete(list);
        countUserArea+=u;
        countProduct+=n;
        countProductArea+=k;
        countProductPushLog+=m;
        countArea+=a;
        System.out.println("更新用户地址："+u+"条 "+"更新商品："+n+"条 "+"更新商品地址："+k+"条 "+"更新商品上架记录："+m+"条 "+"删除地址："+a+"条");
    }
}
